package com.ericsson.web.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;

public class HSQLDatabase implements IDatabase {
	
	private static HSQLDatabase db = null;
	private Connection conn = null;
	
	private final static String COOKIE_TABLE_NAME = "COOKIES";
	private final static String USER_TABLE_NAME = "USER";
	private final static String USER_SESSION_NAME = "SESSIONS";
	
	private HSQLDatabase()  {
		createTables();
	}
	
	public static HSQLDatabase getInstance() {
		if(db == null) {
			db = new HSQLDatabase();
		}
		return db;
	}
	
	private Connection getConnection() throws SQLException, ClassNotFoundException {
		if(conn == null) {
			Class.forName("org.hsqldb.jdbcDriver");
			String connectString  = "jdbc:hsqldb:hsql://127.0.0.1:" + 
				Constants.getDbPort() + "/" + Constants.getDbName();
			System.out.println(connectString);
			conn = DriverManager.getConnection(connectString
					, "sa", "");
		}
		return  conn;
	}
	
	@Override
	public void saveCookies(Map cookies) {
		StringBuffer strCookie = new StringBuffer();
		for(Object key : cookies.keySet()) {
			strCookie.append(key.toString());
			strCookie.append("=");
			String[] value = (String[])cookies.get(key);
			for(String s : value)  {
				strCookie.append(s);
			}
			strCookie.append(";");
		}

		String sql = "INSERT INTO " + COOKIE_TABLE_NAME + "(ID, COOKIE) VALUES(NULL,  ?);";
		try {
			PreparedStatement pstat = getConnection().prepareStatement(sql);
			pstat.setString(1, strCookie.toString());
			pstat.executeUpdate();
			pstat.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private void executeUpdate(String sql) {
		try {
			Statement stmt = getConnection().createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private void  createTables() {
		createTableIfNotExist(COOKIE_TABLE_NAME,  
				"(ID INTEGER GENERATED BY DEFAULT AS IDENTITY," + 
				" COOKIE VARCHAR(200))");
		
		createTableIfNotExist(USER_TABLE_NAME, 
				"(ID INTEGER GENERATED BY DEFAULT AS IDENTITY," +
				" USERNAME VARCHAR(20) UNIQUE," +
				" PASSWORD VARCHAR(50)," +
				" DESCRIPTION VARCHAR(5000))");
		
		createTableIfNotExist(USER_SESSION_NAME,
				"(ID INTEGER GENERATED BY DEFAULT AS IDENTITY," +
				"USERID INTEGER," +
				"SID VARCHAR(128)," +
				"TOKEN VARCHAR(128))");
	}
	
	private void createTableIfNotExist(String tableName, String tableDesc) {
		String sql = "SELECT COUNT(*) FROM information_schema.system_tables " +
				"WHERE table_schem = 'PUBLIC' and table_name = '" + tableName + "';";
		int isExist = getSingleIntegerResult(sql);
		if(isExist <= 0) {
			sql = "CREATE TABLE " + tableName + " " + tableDesc + ";";
			executeUpdate(sql);
		}
	}

	@Override
	public Map<Integer, String> getAllStolenCookies() {
		Map<Integer, String> result = new Hashtable<Integer, String>();
		String sql = "SELECT * FROM " + COOKIE_TABLE_NAME;
		try {
			Statement  stmt = getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				result.put(rs.getInt(1), rs.getString(2));
			}
		} catch(Exception e)  {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public void cleanCookie() {
		String sql = "DELETE FROM " + COOKIE_TABLE_NAME;
		System.out.println(sql);
		executeUpdate(sql);
	}

	private final static String REALM = "Robin";
	
	@Override
	public int loginAuth(String username, String password) {
		int userId = -1;
		String sql = "SELECT ID, PASSWORD FROM " + USER_TABLE_NAME + 
			" WHERE USERNAME=?;";
		System.out.println(sql);
		try {
			PreparedStatement pstmt = getConnection().prepareStatement(sql);
			pstmt.setString(1, username);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				String ck  = rs.getString(2);
				if(ck.equals(Utils.md5(REALM + password))){
					userId = rs.getInt(1);
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return userId;
	}

	private String getSingleStringResult(String sql) {
		String result = "";
		try {
			Statement  stmt = getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				result = rs.getString(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private int getSingleIntegerResult(String sql) {
		int result = -1;
		try {
			Statement  stmt = getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				result = rs.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	@Override
	public int registerUser(String username, String password, String description) {
		String md5 = Utils.md5(REALM + password);
		String sql = "INSERT INTO " + USER_TABLE_NAME +
				" (ID, USERNAME, PASSWORD, DESCRIPTION) VALUES " +
				"(NULL, ?, ?, ?);";
		try {
			PreparedStatement pstat = getConnection().prepareStatement(sql);
			pstat.setString(1, username);
			pstat.setString(2, md5);
			pstat.setString(3, description);
			pstat.executeUpdate();
			pstat.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return loginAuth(username, password);
	}

	@Override
	public String getUserNameById(int id) {
		String sql = "SELECT USERNAME FROM " + USER_TABLE_NAME +
			" WHERE ID=" + id + ";";
		return getSingleStringResult(sql);
	}

	@Override
	public String getDescriptionById(int id) {
		String sql = "SELECT DESCRIPTION FROM " + USER_TABLE_NAME +
			" WHERE ID="  + id + ";";
		return getSingleStringResult(sql);
	}

	@Override
	public Map<Integer, String> getAllUsersExceptMe(int id) {
		Map<Integer, String> users = new HashMap<Integer, String>();
		String sql = "SELECT ID, USERNAME FROM " + USER_TABLE_NAME +
			" WHERE ID<>" + id + ";";
		try {
			Statement  stmt = getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				users.put(rs.getInt(1), rs.getString(2));
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return users;
	}

	@Override
	public void changeDescription(int id, String description) {
		String  sql = "UPDATE " + USER_TABLE_NAME + 
		" SET DESCRIPTION=? WHERE ID=" + id + ";";
		try {
			PreparedStatement pstat = getConnection().prepareStatement(sql);
			pstat.setString(1, description);
			pstat.executeUpdate();
			pstat.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void saveSession(SessionData sd) {
		String sql = "INSERT INTO " + USER_SESSION_NAME + 
			"(ID, USERID, SID, TOKEN) VALUES " +
			"(null, " + sd.getUserId() + ", '" + sd.getSid() + "', '" + 
			sd.getToken() + "');";
		executeUpdate(sql);
	}

	@Override
	public String validate(String userName, String sid, String token) {
		String newToken = "";
		int userId = getUserIdByUserName(userName);
		if(userId >= 0) {
			String sql = "SELECT SID, TOKEN FROM " + USER_SESSION_NAME + 
			" WHERE USERID=" + userId + ";";
			System.out.println(sql);
			try {
				Statement  stmt = getConnection().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()) {
					if(rs.getString(1).equals(sid) && rs.getString(2).equals(token)) {
						newToken = SessionData.generateRandomId();
					}
				}
				if(!newToken.isEmpty()) {
					sql = "UPDATE " + USER_SESSION_NAME + 
					" SET TOKEN='" + newToken + "' " +
							"WHERE USERID=" + userId + 
							" AND SID='" + sid + "';";
					executeUpdate(sql);
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		return newToken;
	}

	@Override
	public int getUserIdByUserName(String userName) {
		String sql = "SELECT ID FROM " + USER_TABLE_NAME + 
			" WHERE USERNAME=?;";
		int result = -1;
		try {
			PreparedStatement pstmt = getConnection().prepareStatement(sql);
			pstmt.setString(1, userName);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				result = rs.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public void removeSession(int userId, String sid) {
		String sql = "DELETE FROM " + USER_SESSION_NAME + 
			" WHERE USERID=" + userId + " AND SID='" + sid + "';";
		executeUpdate(sql);
	}

	@Override
	public Map<Integer, String> searchFiendByUserName(String search) {
		Map<Integer, String> users = new HashMap<Integer, String>();
		String sql = "SELECT ID, USERNAME FROM " + USER_TABLE_NAME +
			" WHERE USERNAME like ?;";
		try {
			PreparedStatement pstmt = getConnection().prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				users.put(rs.getInt(1), rs.getString(2));
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return users;
	}
	
}
